#region Copyright (c) 2006-2011 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2011 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.ProjectItemGenerators;

namespace Widgetsphere.Generator.DALProxy.ProjectItemGenerators.DataTransferExtensionObject
{
	class DTOExtensionObjectGeneratedTemplate : BaseDALProxyTemplate
	{
		private readonly StringBuilder sb = new StringBuilder();
		private readonly Table _currentTable;

		public DTOExtensionObjectGeneratedTemplate(ModelRoot model, Table currentTable)
		{
			_model = model;
			_currentTable = currentTable;
		}

		#region BaseClassTemplate overrides
		public override string FileName
		{
			get { return string.Format("{0}.Generated.cs", _currentTable.PascalName); }
		}

		public string ParentItemName
		{
			get { return string.Format("{0}.cs", _currentTable.PascalName); }
		}

		public override string FileContent
		{
			get
			{
				GenerateContent();
				return sb.ToString();
			}
		}

		#endregion

		#region GenerateContent

		private void GenerateContent()
		{
			try
			{
				Widgetsphere.Generator.GenerationHelper.AppendCopyrightInCode(sb, _model);
				this.AppendUsingStatements();
				sb.AppendLine("namespace " + this.GetLocalNamespace() + ".Extensions");
				sb.AppendLine("{");
				this.AppendClass();
				sb.AppendLine("}");
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		#endregion

		#region namespace / objects

		public void AppendUsingStatements()
		{
			sb.AppendLine("using System;");
			sb.AppendLine("using System.Collections.Generic;");
			sb.AppendLine("using System.Linq;");
			sb.AppendLine("using System.Text;");
			sb.AppendLine("using System.Xml;");
			sb.AppendLine("using System.ComponentModel;");
			sb.AppendLine("using System.Data;");
			sb.AppendLine("using Widgetsphere.Core.DataAccess;");
			sb.AppendLine("using System.Linq.Expressions;");
			sb.AppendLine("using Widgetsphere.Core.Util;");
			sb.AppendLine("using " + this.DefaultNamespace + ".Business.Views;");
			sb.AppendLine();
		}

		private void AppendClass()
		{
			try
			{
				sb.AppendLine("	/// <summary>");
				sb.AppendLine("	/// This is a set of extensions for the " + _currentTable.PascalName + " DAL/DTO object");
				sb.AppendLine("	/// </summary>");
				sb.AppendLine("	public static partial class " + _currentTable.PascalName + "Extension");
				sb.AppendLine("	{");

				#region Fill DAL
				sb.AppendLine("		#region Fill DAL");
				sb.AppendLine();
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Populate a DAL object from its corresponding DTO.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		/// <param name=\"item\">The empty DAL object.</param>");
				sb.AppendLine("		/// <param name=\"dtoItem\">The source DTO to load.</param>");
				sb.AppendLine("		public static void Fill(this " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item, " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + " dtoItem)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (dtoItem == null) throw new Exception(\"The " + _currentTable.PascalName + " DTO cannot be null.\");");
				sb.AppendLine("			if (item == null) throw new Exception(\"The " + _currentTable.PascalName + " item cannot be null.\");");
				sb.AppendLine("			var dr = ((DataRow)((IWrappingClass)item).WrappedClass);");
				foreach (var column in _currentTable.GetColumnsFullHierarchy().Where(x => x.Generated))
				{
					if (column.AllowNull)
					{
						sb.AppendLine("			if (dtoItem." + column.PascalName + " == null) dr[\"" + column.DatabaseName + "\"] = System.DBNull.Value;");
						sb.AppendLine("			else dr[\"" + column.DatabaseName + "\"] = dtoItem." + column.PascalName + ";");
					}
					else
					{
						sb.AppendLine("			dr[\"" + column.DatabaseName + "\"] = dtoItem." + column.PascalName + ";");
					}
				}

				sb.AppendLine("		}");
				sb.AppendLine();
				sb.AppendLine("		#endregion");
				sb.AppendLine();
				#endregion

				#region Fill DTO

				sb.AppendLine("		#region Fill DTO");
				sb.AppendLine();
				//Fill for DTO collection
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Populate a DTO object collection from its corresponding DAL object collection.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		/// <param name=\"list\">The destination DTO collection.</param>");
				sb.AppendLine("		/// <param name=\"" + _currentTable.CamelName + "Collection\">The source DAL collection.</param>");
				sb.AppendLine("		public static void Fill(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> list, " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection " + _currentTable.CamelName + "Collection)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (" + _currentTable.CamelName + "Collection == null) throw new Exception(\"The " + _currentTable.PascalName + " collection cannot be null.\");");
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item in " + _currentTable.CamelName + "Collection)");
				sb.AppendLine("			{");
				sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "();");
				sb.AppendLine("				newItem.Fill(item);");
				sb.AppendLine("				list.Add(newItem);");
				sb.AppendLine("			}");
				sb.AppendLine("		}");
				sb.AppendLine();

				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Populate a DTO object from its corresponding DAL object.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		/// <param name=\"item\">The source DAL object.</param>");
				sb.AppendLine("		/// <param name=\"dtoItem\">The empty DTO to load.</param>");
				sb.AppendLine("		public static void Fill(this " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + " dtoItem, " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (dtoItem == null) throw new Exception(\"The " + _currentTable.PascalName + " DTO cannot be null.\");");
				sb.AppendLine("			if (item == null) throw new Exception(\"The " + _currentTable.PascalName + " item cannot be null.\");");

				var allColumns = _currentTable.GetColumnsFullHierarchy();
				foreach (var column in allColumns.Where(x => x.Generated))
				{
					var tableCompositeList = _currentTable.GetTableCompositesFullHierarchy(true);
					var fullComponentColumnList = new List<Column>();
					foreach (var TableComposite in tableCompositeList)
					{
						fullComponentColumnList.AddRange(_currentTable.CompositeList.GetAllColumns(true));
					}

					//If this column is part of a component then hide it
					var q = from x in _currentTable.CompositeList.GetAllColumns(true)
									where x == column
									select x;

					if (q.FirstOrDefault() == null)
					{
						//Normal property
						sb.AppendLine("			dtoItem." + column.PascalName + " = item." + column.PascalName + ";");
					}
				}

				//Set properties in components
				foreach (TableComposite TableComposite in _currentTable.CompositeList)
				{
					foreach (Reference reference in TableComposite.Columns)
					{
						var column = (Column)reference.Object;
						sb.AppendLine("			dtoItem." + column.PascalName + " = item." + TableComposite.PascalName + "Item." + column.PascalName + ";");
					}
				}

				if (_currentTable.AllowCreateAudit)
				{
					sb.AppendLine("			dtoItem." + _model.Database.CreatedDatePascalName + " = item." + _model.Database.CreatedDatePascalName + ";");
					sb.AppendLine("			dtoItem." + _model.Database.CreatedByPascalName + " = item." + _model.Database.CreatedByPascalName + ";");
				}
				if (_currentTable.AllowModifiedAudit)
				{
					sb.AppendLine("			dtoItem." + _model.Database.ModifiedDatePascalName + " = item." + _model.Database.ModifiedDatePascalName + ";");
					sb.AppendLine("			dtoItem." + _model.Database.ModifiedByPascalName + " = item." + _model.Database.ModifiedByPascalName + ";");
				}

				sb.AppendLine("		}");
				sb.AppendLine();
				sb.AppendLine("		#endregion");
				sb.AppendLine();
				#endregion

				#region RunSelect
				sb.AppendLine("		#region RunSelect");
				sb.AppendLine();
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Select all objects from store.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		public static void RunSelect(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> list)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			var retval = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.RunSelect();");
				sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item in retval)");
				sb.AppendLine("			{");
				sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "();");
				sb.AppendLine("				newItem.Fill(item);");
				sb.AppendLine("				list.Add(newItem);");
				sb.AppendLine("			}");
				sb.AppendLine("		}");
				sb.AppendLine();

				//RunSelect LINQ
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Using the specified Where expression, execute a query against the database to return a result set.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		public static void RunSelect(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> list, Expression<Func<" + this.DefaultNamespace + ".Business.LINQ." + _currentTable.PascalName + "Query, bool>> where)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			list.Fill(" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.RunSelect(where));");
				sb.AppendLine("		}");
				sb.AppendLine();

				sb.AppendLine("		#endregion");
				sb.AppendLine();
				#endregion

				#region RunSelect Paged

				sb.AppendLine("		#region RunSelect Paged");
				sb.AppendLine();
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Using the specified Where expression, execute a query against the database to return a paged result set.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		public static " + this.DefaultNamespace + ".DataTransfer.PagedQueryResults<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> RunSelect(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> list, string pageSize, string page, string sortOrder, string sortColumn, string linq)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			#region Setup Variables and Error Check");
				sb.AppendLine("			int po;");
				sb.AppendLine("			int rpp;");
				sb.AppendLine();
				sb.AppendLine("			if (!int.TryParse(page, out po)) throw new Exception(\"The page number number is not a valid integer!\");");
				sb.AppendLine("			if (po < 1) po = 1;");
				sb.AppendLine();
				sb.AppendLine("			if (!int.TryParse(pageSize, out rpp)) throw new Exception(\"The page size is not a valid integer!\");");
				sb.AppendLine("			if (rpp < 1) rpp = 1;");
				sb.AppendLine();
				sb.AppendLine("			" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".FieldNameConstants sortField;");
				sb.AppendLine("			try");
				sb.AppendLine("			{");
				sb.AppendLine("				sortField = (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".FieldNameConstants)Enum.Parse(typeof(" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".FieldNameConstants), sortColumn);");
				sb.AppendLine("			}");
				sb.AppendLine("			catch");
				sb.AppendLine("			{");
				sb.AppendLine("				throw new Exception(\"The sort field is not valid!\");");
				sb.AppendLine("			}");
				sb.AppendLine();
				sb.AppendLine("			bool ascending = (sortOrder.ToLower() == \"asc\" || sortOrder.ToLower() == \"ascending\");");
				sb.AppendLine("			#endregion");
				sb.AppendLine();
				sb.AppendLine("			var retVal = new " + this.DefaultNamespace + ".DataTransfer.PagedQueryResults<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			var paging = new " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Paging();");
				sb.AppendLine("			paging.RecordsperPage = rpp;");
				sb.AppendLine("			paging.PageIndex = po;");
				sb.AppendLine("			paging.OrderByList.Add(new " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "PagingFieldItem(sortField, ascending));");
				sb.AppendLine();
				sb.AppendLine("			" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection dalList = null;");
				sb.AppendLine("			if (string.IsNullOrEmpty(linq))");
				sb.AppendLine("			{");
				sb.AppendLine("				dalList = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.RunSelect(x => true, paging);");
				sb.AppendLine("			}");
				sb.AppendLine("			else");
				sb.AppendLine("			{");
				//sb.AppendLine("				System.Reflection.MethodInfo method = LINQDynamicCompile.GetLINQMethodPaged(linq, \"" + this.DefaultNamespace + ".Business\", \"" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "\");");
				sb.AppendLine("				System.Reflection.MethodInfo method = LINQDynamicCompile.GetLINQMethodPaged(linq, \"" + this.DefaultNamespace + ".Business\", \"" + _currentTable.PascalName + "\");");
				sb.AppendLine("				dalList = (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection)method.Invoke(null, new object[] { paging });");
				sb.AppendLine("			}");
				sb.AppendLine();
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item in dalList)");
				sb.AppendLine("			{");
				sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "();");
				sb.AppendLine("				newItem.Fill(item);");
				sb.AppendLine("				list.Add(newItem);");
				sb.AppendLine("			}");
				sb.AppendLine();
				sb.AppendLine("			retVal.CurrentPage = paging.PageIndex;");
				sb.AppendLine("			double totalPages = Math.Ceiling((double)paging.RecordCount / (double)paging.RecordsperPage);");
				sb.AppendLine("			retVal.TotalPages = (int)totalPages;");
				sb.AppendLine("			retVal.TotalRecords = paging.RecordCount;");
				sb.AppendLine("			retVal.GridData = list;");
				sb.AppendLine();
				sb.AppendLine("			return retVal;");
				sb.AppendLine("		}");
				sb.AppendLine();
				sb.AppendLine("		#endregion");
				sb.AppendLine();

				#endregion

				#region RunSelect Paged

				sb.AppendLine("		#region RunSelect Paged");
				sb.AppendLine();
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Using the specified Where expression, execute a query against the database to return a result set.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		public static void RunSelect(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> list, string linq)");
				sb.AppendLine("		{");
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine();
				sb.AppendLine("			" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection dalList = null;");
				sb.AppendLine("			if (string.IsNullOrEmpty(linq))");
				sb.AppendLine("			{");
				sb.AppendLine("				dalList = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.RunSelect(x => true);");
				sb.AppendLine("			}");
				sb.AppendLine("			else");
				sb.AppendLine("			{");
				sb.AppendLine("				System.Reflection.MethodInfo method = LINQDynamicCompile.GetLINQMethodAll(linq, \"" + this.DefaultNamespace + ".Business\", \"" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "\");");
				sb.AppendLine("				dalList = (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection)method.Invoke(null, new object[] { });");
				sb.AppendLine("			}");
				sb.AppendLine();
				sb.AppendLine("			if (list == null) list = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
				sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item in dalList)");
				sb.AppendLine("			{");
				sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "();");
				sb.AppendLine("				newItem.Fill(item);");
				sb.AppendLine("				list.Add(newItem);");
				sb.AppendLine("			}");
				sb.AppendLine("		}");
				sb.AppendLine();
				sb.AppendLine("		#endregion");
				sb.AppendLine();

				#endregion

				#region Persist

				if (!_currentTable.Immutable)
				{
					sb.AppendLine("		#region Persist");
					sb.AppendLine();
					sb.AppendLine("		/// <summary>");
					sb.AppendLine("		/// Persists this object to store.");
					sb.AppendLine("		/// </summary>");
					sb.AppendLine("		public static " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + " Persist(this " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + " " + _currentTable.CamelName + ")");
					sb.AppendLine("		{");
					sb.AppendLine("			if (" + _currentTable.CamelName + " == null) throw new Exception(\"The " + _currentTable.PascalName + " DTO cannot be null.\");");
					sb.AppendLine("			//Find this object and if not exists, create a new one");
					sb.AppendLine("			" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " newItem = null;");
					sb.AppendLine("			" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection newCollection = null;");
					sb.AppendLine();
					if (_currentTable.FullHierarchyPrimaryKeyIsDatabaseIdentity())
					{
						sb.AppendLine("			bool isIdentityChecked = true;");
						sb.AppendLine("			if(isIdentityChecked)");
						sb.AppendLine("			{");
						sb.AppendLine("				if (" + _currentTable.CamelName + "." + _currentTable.PrimaryKeyColumns[0].PascalName + " > 0)");
						sb.AppendLine("				{");
						sb.Append("					newItem = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".SelectUsingPK(");

						var jj = 0;
						foreach (var column in _currentTable.PrimaryKeyColumns.OrderBy(x => x.Name))
						{
							sb.Append(_currentTable.CamelName + "." + column.PascalName);
							if (jj < _currentTable.PrimaryKeyColumns.Count - 1)
								sb.Append(", ");
							jj++;
						}
						sb.AppendLine(");");
						sb.AppendLine("					newCollection = newItem.ParentCollection;");
						sb.AppendLine("				}");
						sb.AppendLine("				else");
						sb.AppendLine("				{");
						sb.AppendLine("					if (" + _currentTable.CamelName + "." + _currentTable.PrimaryKeyColumns[0].PascalName + " == 0) " + _currentTable.CamelName + "." + _currentTable.PrimaryKeyColumns[0].PascalName + " = -1;");
						sb.AppendLine("					newCollection = new " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection();");
						sb.AppendLine("					newItem = newCollection.NewItem();");
						sb.AppendLine("				}");
						sb.AppendLine("			}");
					}
					else
					{
						sb.AppendLine("			if (false) {}");
					}
					sb.AppendLine("			else");
					sb.AppendLine("			{");
					sb.Append("				newItem = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".SelectUsingPK(");
					
					var ii = 0;
					foreach (var column in _currentTable.PrimaryKeyColumns.OrderBy(x => x.Name))
					{
						sb.Append(_currentTable.CamelName + "." + column.PascalName + "");
						if (ii < _currentTable.PrimaryKeyColumns.Count - 1)
							sb.Append(", ");
						ii++;
					}
					sb.AppendLine(");");
					sb.AppendLine("				if (newItem == null)");
					sb.AppendLine("				{");
					sb.AppendLine("					newCollection = new " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection();");
					sb.AppendLine("					newItem = newCollection.NewItem();");
					sb.AppendLine("				}");
					sb.AppendLine("				else");
					sb.AppendLine("				{");
					sb.AppendLine("					newCollection = newItem.ParentCollection;");
					sb.AppendLine("				}");
					sb.AppendLine("			}");
					sb.AppendLine();
					sb.AppendLine("			//Populate the DAL and perist");
					sb.AppendLine("			newItem.Fill(" + _currentTable.CamelName + ");");
					sb.AppendLine("			if (!newItem.IsParented) newCollection.AddItem(newItem);");
					sb.AppendLine("			newCollection.Persist();");
					sb.AppendLine();
					sb.AppendLine("			//Re-populate the passed-in object from the DAL");
					sb.AppendLine("			" + _currentTable.CamelName + ".Fill(newItem);");
					sb.AppendLine("			return " + _currentTable.CamelName + ";");
					sb.AppendLine("		}");
					sb.AppendLine();
					sb.AppendLine("		/// <summary>");
					sb.AppendLine("		/// Persists this collection to store.");
					sb.AppendLine("		/// </summary>");
					sb.AppendLine("		public static bool Persist(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> " + _currentTable.CamelName + "List)");
					sb.AppendLine("		{");
					sb.AppendLine("			if (" + _currentTable.CamelName + "List == null) throw new Exception(\"The " + _currentTable.PascalName + " DTO list cannot be null.\");");
					sb.AppendLine("			//Get all objects from the database that match PK");
					sb.AppendLine("			var newCollection = new " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection();");
					sb.AppendLine("			var keyList = new List<" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "PrimaryKey>();");
					sb.AppendLine("			foreach (var dto in " + _currentTable.CamelName + "List)");
					sb.AppendLine("			{");
					sb.Append("				keyList.Add(new " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "PrimaryKey(");

					ii = 0;
					foreach (var column in _currentTable.PrimaryKeyColumns.OrderBy(x => x.Name))
					{
						sb.Append("dto." + column.PascalName);
						if (ii < _currentTable.PrimaryKeyColumns.Count - 1)
							sb.Append(", ");
						ii++;
					}

					sb.AppendLine("));");
					sb.AppendLine("			}");
					sb.AppendLine("			newCollection.SubDomain.AddSelectCommand(new " + this.DefaultNamespace + ".Business.SelectCommands." + _currentTable.PascalName + "SelectByPks(keyList));");
					sb.AppendLine("			newCollection.SubDomain.RunSelectCommands();");
					sb.AppendLine();
					sb.AppendLine("			//Now find and populate the objects for saving");
					sb.AppendLine("			foreach (var dto in " + _currentTable.CamelName + "List)");
					sb.AppendLine("			{");
					sb.AppendLine("				var " + _currentTable.CamelName + " = (from x in (IEnumerable<" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ">)newCollection");

					ii = 0;
					foreach (var column in _currentTable.PrimaryKeyColumns.OrderBy(x => x.Name))
					{
						if (ii == 0) sb.Append("					where ");
						sb.Append("x." + column.PascalName + " == dto." + column.PascalName);
						if (ii < _currentTable.PrimaryKeyColumns.Count - 1)
							sb.Append(" && ");
						ii++;
					}
					sb.AppendLine();
					sb.AppendLine("					select x).FirstOrDefault();");

					sb.AppendLine();
					sb.AppendLine("				if (" + _currentTable.CamelName + " == null) " + _currentTable.CamelName + " = newCollection.NewItem();");
					sb.AppendLine("				" + _currentTable.CamelName + ".Fill(dto);");
					sb.AppendLine("				if (!" + _currentTable.CamelName + ".IsParented) newCollection.AddItem(" + _currentTable.CamelName + ");");
					sb.AppendLine("			}");
					sb.AppendLine("			newCollection.Persist();");
					sb.AppendLine();
					sb.AppendLine("			//Re-populate the passed-in object from the DAL");
					sb.AppendLine("			var deletedList = new List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + ">();");
					sb.AppendLine("			foreach (var dto in " + _currentTable.CamelName + "List)");
					sb.AppendLine("			{");
					sb.AppendLine("				var " + _currentTable.CamelName + " = (from x in (IEnumerable<" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ">)newCollection");

					ii = 0;
					foreach (var column in _currentTable.PrimaryKeyColumns.OrderBy(x => x.Name))
					{
						if (ii == 0) sb.Append("					where ");
						sb.Append("x." + column.PascalName + " == dto." + column.PascalName);
						if (ii < _currentTable.PrimaryKeyColumns.Count - 1)
							sb.Append(" && ");
						ii++;
					}
					sb.AppendLine();

					sb.AppendLine("					select x).FirstOrDefault();");
					sb.AppendLine();
					sb.AppendLine("				if (" + _currentTable.CamelName + " != null) dto.Fill(" + _currentTable.CamelName + ");");
					sb.AppendLine("				else deletedList.Add(dto);");
					sb.AppendLine("			}");
					sb.AppendLine();
					sb.AppendLine("			//Remove the items that are no longer there (if any)");
					sb.AppendLine("			foreach (var dto in deletedList)");
					sb.AppendLine("			{");
					sb.AppendLine("				" + _currentTable.CamelName + "List.Remove(dto);");
					sb.AppendLine("			}");
					sb.AppendLine();
					sb.AppendLine("			return true;");
					sb.AppendLine("		}");
					sb.AppendLine();
					sb.AppendLine("		#endregion");
					sb.AppendLine();
				}

				#endregion

				#region SelectByField

				sb.AppendLine("		#region " + _currentTable.PascalName + " Select by Column");
				sb.AppendLine();
				foreach (var column in _currentTable.GetColumnsSearchable(true))
				{
					sb.AppendLine("		/// <summary>");
					sb.AppendLine("		/// Select an object list by '" + column.PascalName + "' field.");
					sb.AppendLine("		/// </summary>");
					sb.AppendLine("		public static void SelectBy" + column.PascalName + "Field(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> " + _currentTable.CamelName + "List, " + column.GetCodeType(true) + " " + column.CamelName + ")");
					sb.AppendLine("		{");
					sb.AppendLine("			var list = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.SelectBy" + column.PascalName + "(" + column.CamelName + ");");
					sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item in list)");
					sb.AppendLine("			{");
					sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "();");
					sb.AppendLine("				newItem.Fill(item);");
					sb.AppendLine("				" + _currentTable.CamelName + "List.Add(newItem);");
					sb.AppendLine("			}");
					sb.AppendLine("		}");
					sb.AppendLine();
				}
				sb.AppendLine("		#endregion");
				sb.AppendLine();

				#endregion

				#region SelectUsingPK

				sb.AppendLine("		#region " + _currentTable.PascalName + " SelectUsingPK");
				sb.AppendLine();
				sb.AppendLine("		/// <summary>");
				sb.AppendLine("		/// Select a single object from this collection by its primary key.");
				sb.AppendLine("		/// </summary>");
				sb.AppendLine("		public static bool SelectUsingPK(this " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + " " + _currentTable.CamelName + ", " + PrimaryKeyColumnList(_currentTable, true) + ")");
				sb.AppendLine("		{");
				sb.AppendLine("			var item = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".SelectUsingPK(" + PrimaryKeyColumnList(_currentTable, false) + ");");
				sb.AppendLine("			if (item == null) return false;");
				sb.AppendLine("			" + _currentTable.CamelName + ".Fill(item);");
				sb.AppendLine("			return true;");
				sb.AppendLine("		}");
				sb.AppendLine();
				sb.AppendLine("		#endregion");
				sb.AppendLine();

				#endregion

				#region SelectByFK

				sb.AppendLine("		#region " + _currentTable.PascalName + " Select By Foreign Key");
				sb.AppendLine();
				var allRelations = _currentTable.GetRelationsFullHierarchy();
				var methodList = new List<string>();
				foreach (var relation in allRelations)
				{
					if (!relation.ParentTable.Generated || !relation.ChildTable.Generated)
					{
						//Do Nothing
					}
					else
					{
						var childTable = relation.ChildTable;
						//Handle relations by inheritance
						if (_currentTable.IsInheritedFrom(childTable))
							childTable = _currentTable;

						if (childTable.IsInheritedFrom(relation.ParentTable))
						{
							//Do Nothing
						}

						if (childTable == _currentTable)
						{
							var methodName = "SelectBy" + relation.PascalRoleName + relation.ParentTable.PascalName;
							if (!methodList.Contains(methodName))
							{
								methodList.Add(methodName);

								sb.AppendLine("		/// <summary>");
								sb.AppendLine("		/// Populate a '" + relation.ParentTable.PascalName + "' DTO object collection from its relationship with the '" + childTable.PascalName + "' table.");
								sb.AppendLine("		/// </summary>");
								sb.AppendLine("		public static void " + methodName + "(this List<" + this.DefaultNamespace + ".DataTransfer." + childTable.PascalName + "> " + childTable.PascalName + "List, " + PrimaryKeyColumnList(relation.ParentTable, true) + ")");
								sb.AppendLine("		{");
								sb.Append("			var list = " + this.DefaultNamespace + ".Business.Objects." + childTable.PascalName + "Collection.RunSelect(x => ");
								foreach (ColumnRelationship columnRelationship in relation.ColumnRelationships)
								{
									var parentColumn = columnRelationship.ParentColumnRef.Object as Column;
									var childColumn = columnRelationship.ChildColumnRef.Object as Column;
									sb.Append("x." + childColumn.PascalName + " == " + parentColumn.CamelName + "");
									if (relation.ColumnRelationships.IndexOf(columnRelationship) < relation.ColumnRelationships.Count - 1)
										sb.Append(" && ");
								}
								sb.AppendLine(");");
								sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + childTable.PascalName + " item in list)");
								sb.AppendLine("			{");
								sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + childTable.PascalName + "();");
								sb.AppendLine("				newItem.Fill(item);");
								sb.AppendLine("				" + childTable.PascalName + "List.Add(newItem);");
								sb.AppendLine("			}");
								sb.AppendLine("		}");
								sb.AppendLine();
							}
						}
						else if ((relation.ParentTable == _currentTable) && relation.IsManyToMany)
						{
							var otherTable = relation.GetSecondaryAssociativeTable();

							var methodName = "SelectBy" + relation.PascalRoleName + otherTable.PascalName;
							if (!methodList.Contains(methodName))
							{
								methodList.Add(methodName);

								sb.AppendLine("		/// <summary>");
								sb.AppendLine("		/// Populate a '" + _currentTable.PascalName + "' DTO object collection from its relationship with the '" + otherTable.PascalName + "' table.");
								sb.AppendLine("		/// </summary>");
								sb.AppendLine("		public static void " + methodName + "(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> " + _currentTable.CamelName + "List, " + PrimaryKeyColumnList(otherTable, true) + ")");
								sb.AppendLine("		{");
								sb.Append("			var list = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.RunSelect(x => ");

								var otherRelation = relation.GetAssociativeOtherRelation();
								foreach (ColumnRelationship columnRelationship in otherRelation.ColumnRelationships)
								{
									var columnLeft = columnRelationship.ChildColumnRef.Object as Column;
									var columnRight = columnRelationship.ParentColumnRef.Object as Column;

									sb.Append("x." + relation.PascalRoleName + childTable.PascalName + "." + columnLeft.PascalName + " == " + columnRight.CamelName + "");
									if (otherRelation.ColumnRelationships.IndexOf(columnRelationship) < otherRelation.ColumnRelationships.Count - 1)
										sb.Append(" && ");
								}
								sb.AppendLine(");");
								sb.AppendLine("			foreach (" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + " item in list)");
								sb.AppendLine("			{");
								sb.AppendLine("				var newItem = new " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "();");
								sb.AppendLine("				newItem.Fill(item);");
								sb.AppendLine("				" + _currentTable.CamelName + "List.Add(newItem);");
								sb.AppendLine("			}");
								sb.AppendLine("		}");
								sb.AppendLine();
							}

						}
					}
				}

				sb.AppendLine("		#endregion");
				sb.AppendLine();

				#endregion

				#region Select By Custom Select Commands

				sb.AppendLine("		#region Region Select By Custom Retrieve Rules");
				sb.AppendLine();
				foreach (Reference reference in _currentTable.CustomRetrieveRules)
				{
					var rule = (CustomRetrieveRule)reference.Object;
					if (rule.Generated)
					{
						sb.AppendLine("		/// <summary>");
						sb.AppendLine("		/// Select rows based on the '" + rule.PascalName + "' custom retrieve rule for the '" + _currentTable.DatabaseName + "' table.");
						sb.AppendLine("		/// </summary>");
						sb.Append("		public static void SelectBy" + rule.PascalName + "(this List<" + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + "> list");
						
						var parameterList = rule.GetParameters();
						foreach (var parameter in parameterList)
						{
							sb.Append(", " + parameter.GetCodeType(true) + " " + parameter.CamelName);
						}

						sb.AppendLine(")");
						sb.AppendLine("		{");
						sb.Append("			list.Fill(" + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + "Collection.SelectBy" + rule.PascalName + "(");

						foreach (var parameter in parameterList)
						{
							if (parameter.IsOutputParameter) sb.Append("out ");
							sb.Append(parameter.CamelName);
							if (parameterList.IndexOf(parameter) < parameterList.Count() - 1)
								sb.Append(", ");
						}

						sb.AppendLine("));");
						sb.AppendLine("		}");
						sb.AppendLine();
					}
				}

				sb.AppendLine("		#endregion");
				sb.AppendLine();

				#endregion

				#region Delete

				if (!_currentTable.Immutable)
				{
					sb.AppendLine("		#region " + _currentTable.PascalName + " Delete");
					sb.AppendLine();
					sb.AppendLine("		/// <summary>");
					sb.AppendLine("		/// Delete the specified item");
					sb.AppendLine("		/// </summary>");
					sb.AppendLine("		public static bool Delete(this " + this.DefaultNamespace + ".DataTransfer." + _currentTable.PascalName + " " + _currentTable.CamelName + ")");
					sb.AppendLine("		{");
					sb.Append("			var item = " + this.DefaultNamespace + ".Business.Objects." + _currentTable.PascalName + ".SelectUsingPK(");

					var ii = 0;
					foreach (var column in _currentTable.PrimaryKeyColumns.OrderBy(x => x.Name))
					{
						sb.Append(_currentTable.CamelName + "." + column.PascalName);
						if (ii < _currentTable.PrimaryKeyColumns.Count - 1)
							sb.Append(", ");
						ii++;
					}
					sb.AppendLine(");");
					sb.AppendLine("			if (item == null) return false;");
					sb.AppendLine("			item.Delete();");
					sb.AppendLine("			item.Persist();");
					sb.AppendLine("			return true;");
					sb.AppendLine("		}");
					sb.AppendLine();
					sb.AppendLine("		#endregion");
				}

				#endregion

				sb.AppendLine("	}");
				sb.AppendLine();

			}
			catch (Exception ex)
			{
				throw;
			}
		}

		#endregion

		#region Helpers

		private string PrimaryKeyColumnList(Table table, bool includeType)
		{
			var output = new StringBuilder();
			foreach (var dc in table.PrimaryKeyColumns.OrderBy(x => x.Name))
			{
				if (includeType) output.Append(dc.GetCodeType() + " ");
				output.Append(dc.CamelName);
				output.Append(", ");
			}
			var retval = output.ToString();
			if (retval.EndsWith(", ")) retval = retval.Substring(0, retval.Length - 2);
			return retval;
		}

		#endregion

	}
}